package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.*;


public class ReportTitlesDAO implements java.io.Serializable{

	Connection con;

	public ReportTitlesDAO(Connection connection){
		con = connection;
	}

	public ReportTitlesBean selectReportTitlesByPK(ReportTitlesBean bean)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("SELECT REPORT_TITLE,REPORT_DESCRIPTION,REPORT_TITLES_ID, "
				+ "REPORT_CODE,REPORT_NUMBER_OF_COMPANIES_IN_BOOK,REPORT_NUMBER_OF_COMPANIES_IN_DATABASE"
				+ " FROM REPORT_TITLES WHERE REPORT_TITLES_ID = ? ");
		pstmt.setInt(1,bean.getReportTitlesId());
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			ReportTitlesBean bn = new ReportTitlesBean();
			bn.setReportTitle(rs.getString("REPORT_TITLE"));
			bn.setReportDescription(rs.getString("REPORT_DESCRIPTION"));
			bn.setReportTitlesId(rs.getInt("REPORT_TITLES_ID"));
			bn.setReportCode(rs.getString("REPORT_CODE"));
			bn.setNoOfCompaniesInBook(rs.getInt("REPORT_NUMBER_OF_COMPANIES_IN_BOOK"));
			bn.setNoOfCompaniesInDatabase(rs.getInt("REPORT_NUMBER_OF_COMPANIES_IN_DATABASE"));
			return bn;
		}else{
			return null;
		}
	}

	public ReportTitlesBean[] getAllReports()throws SQLException{
		ReportTitlesBean[] lstReports = null;

		Statement stmt =  con.createStatement();
		ResultSet rs =  stmt.executeQuery("select max(REPORT_TITLES_ID) from REPORT_TITLES");
		if(rs.next()){
			lstReports = new ReportTitlesBean[rs.getInt(1) + 1];
		}
		rs.close();

		rs = stmt.executeQuery("SELECT REPORT_TITLES_ID, REPORT_TITLE, REPORT_NUMBER_OF_COMPANIES_IN_BOOK, REPORT_NUMBER_OF_COMPANIES_IN_DATABASE, REPORT_CODE FROM REPORT_TITLES");

		while(rs.next()){
			ReportTitlesBean bn = new ReportTitlesBean();
			bn.setReportTitlesId(rs.getInt(1));
			bn.setReportTitle(rs.getString(2));
			bn.setNoOfCompaniesInBook(rs.getInt(3));
			bn.setNoOfCompaniesInDatabase(rs.getInt(4));
			bn.setReportCode(rs.getString(5));

			lstReports[bn.getReportTitlesId()] = bn;
		}
		rs.close();
		stmt.close();

		return lstReports;
	}

	public DbResultSet selectReportTitlesRecords(String whereClause,String orderByClause)throws SQLException{

		ArrayList records = new ArrayList();
		String sql ="SELECT REPORT_TITLE,REPORT_DESCRIPTION,REPORT_TITLES_ID,CUSTOMISED_INDICATOR,REPORT_NUMBER_OF_COMPANIES_IN_DATABASE FROM REPORT_TITLES";
		if(whereClause !=null && !whereClause.equals(""))sql += " WHERE " + whereClause;
		if(orderByClause !=null && !orderByClause.equals(""))sql += " ORDER BY " + orderByClause;

		System.out.println(sql);

		Statement stmt =  con.createStatement();
		ResultSet rs =  stmt.executeQuery(sql);
		while(rs.next()){
			ReportTitlesBean bn = new ReportTitlesBean(rs.getString("REPORT_TITLE"),rs.getString("REPORT_DESCRIPTION"),rs.getInt("REPORT_TITLES_ID"));
			bn.setCustomReport(rs.getBoolean("CUSTOMISED_INDICATOR"));
			bn.setNoOfCompaniesInDatabase(rs.getInt("REPORT_NUMBER_OF_COMPANIES_IN_DATABASE"));
			records.add(bn);
		}
		rs.close();
		stmt.close();
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}

	public List getReportIdsByCompanyNumber(String registrationNo)throws SQLException{
		List records = new ArrayList();

		String sql = "SELECT MSAL_R_REPORT_NUMBER FROM MSAL_REPORTS WHERE MSAL_R_REGISTERED_NUMBER = ? ORDER BY MSAL_R_REPORT_NUMBER ASC";

		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1,registrationNo);
		ResultSet rs = pstmt.executeQuery();

		while(rs.next()){
			records.add(Integer.valueOf(rs.getInt(1)));
		}

		rs.close();
		pstmt.close();

		return records;
	}

	public ReportTitlesBean addCustomReport(String reportTitle,int userId,String companyIds, String selectedForReports)throws SQLException{
		// Get a new primary key value.
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("select max(REPORT_TITLES_ID) + 1 from REPORT_TITLES");
		rs.next();
		int newId = rs.getInt(1);
		rs.close();
		stmt.close();
		// The custom reports id have a range above 1000000.
		if(newId < 1000000){
			newId = 1000001;
		}

		// Generate a random report code. format Random number of 6-15 digits + WF + REPORT_TITLES_ID, max length 25
		String reportCode = (int)(Math.random()* 1000000000) + "WF" + newId;
		// Insert into the master table.
		addCustomReportTitle(newId,reportTitle,reportCode);

		// Insert into the details table.
		String companies[] = companyIds.split(",");
		String selectedReports[] = selectedForReports.split(",");
		PreparedStatement pstmt = con.prepareStatement("INSERT INTO MSAL_CUSTOMISED_REPORTS(MSAL_CR_REGISTERED_NUMBER,MSAL_CR_REPORT_NUMBER," +
				"MSAL_CR_EXCLUDE_FROM_AVERAGES) VALUES(?,?,?)");
		for(int i=0;i<companies.length;i++){
			pstmt.setString(1,companies[i]);
			pstmt.setInt(2,newId);
			if("true".equals(selectedReports[i])){
				pstmt.setNull(3, Types.CHAR);
			}else{
				pstmt.setString(3, "Y");
			}
			pstmt.addBatch();
			pstmt.clearParameters();
		}
		pstmt.executeBatch();
		pstmt.close();

        ReportTitlesBean bean = new ReportTitlesBean();
        bean.setReportTitlesId(newId);
        bean.setReportTitle(reportTitle);
		bean.setCustomReport(true);
		bean.setReportCode(reportCode);
		return bean;

	}

	public int addCustomReportTitle(int newId, String reportTitle,String reportCode)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("INSERT INTO REPORT_TITLES(REPORT_TITLES_ID,REPORT_TITLE,REPORT_CODE,CUSTOMISED_INDICATOR) VALUES (?,?,?,true)");
		pstmt.setInt(1, newId);
		pstmt.setString(2, reportTitle);
		pstmt.setString(3, reportCode);
		int updateCount = pstmt.executeUpdate();
		pstmt.close();
		return updateCount;
	}

	public int renameReportTitle(int reportId, String reportTitle)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("UPDATE REPORT_TITLES SET REPORT_TITLE = ? WHERE REPORT_TITLES_ID = ?");
		pstmt.setInt(2, reportId);
		pstmt.setString(1, reportTitle);
		int updateCount = pstmt.executeUpdate();
		pstmt.close();
		return updateCount;		
	}

	public String getCompanyIdsByCustomReportId(int reportId)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("SELECT MSAL_CR_REGISTERED_NUMBER FROM MSAL_CUSTOMISED_REPORTS WHERE MSAL_CR_REPORT_NUMBER = ?");
		pstmt.setInt(1, reportId);
		ResultSet rs = pstmt.executeQuery();
		StringBuilder ids = new StringBuilder();
		while(rs.next()){
			ids.append(",'").append(rs.getString(1)).append("'");
		}
		rs.close();
		pstmt.close();

		if(ids.length() > 0){
			return ids.substring(1);
		}else{
			return ids.toString();
		}
	}
	
	public ReportTitlesBean selectReportTitleByFreeReportDownloadRecordId(int recordId) throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("SELECT REPORT_TITLE,REPORT_DESCRIPTION,REPORT_TITLES_ID, "
				+ "REPORT_CODE,REPORT_NUMBER_OF_COMPANIES_IN_BOOK,REPORT_NUMBER_OF_COMPANIES_IN_DATABASE "
				+ "from REPORT_TITLES, EMAIL_DATA where ED_REPORT_NUMBER = REPORT_TITLES_ID and ED_ID = ?");
		pstmt.setInt(1, recordId);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			ReportTitlesBean bn = new ReportTitlesBean();
			bn.setReportTitle(rs.getString("REPORT_TITLE"));
			bn.setReportDescription(rs.getString("REPORT_DESCRIPTION"));
			bn.setReportTitlesId(rs.getInt("REPORT_TITLES_ID"));
			bn.setReportCode(rs.getString("REPORT_CODE"));
			bn.setNoOfCompaniesInBook(rs.getInt("REPORT_NUMBER_OF_COMPANIES_IN_BOOK"));
			bn.setNoOfCompaniesInDatabase(rs.getInt("REPORT_NUMBER_OF_COMPANIES_IN_DATABASE"));
			return bn;
		}else{
			return null;
		}
	}
}